import baostock as bs
import datetime
import pandas as pd
import pymysql

import time
from dateutil import parser

nowTime = datetime.datetime.now().strftime('%Y-%m-%d')
lastTime = (datetime.datetime.now() - datetime.timedelta(days=3000)).strftime('%Y-%m-%d')

db = pymysql.Connect(
    host='127.0.0.1',
    port=3306,
    user='root',
    passwd='',
    db='gupiao',
    charset='utf8'
)

cursor = db.cursor()

sql_dk = "INSERT INTO `fa_stock_hsl` (`date`, `code`, `turn`, `pctChg`, `close`) VALUES"
# 是否部分更新
isbfgx = 0


def saveK2DB(datalist):
    global cursor
    global db
    global isbfgx

    if len(datalist) < 1:
        return False

    sql = sql_dk

    try:
        for j in range(0, len(datalist)):
            if j > 0:
                sql += ","
            data = datalist[j]
            for i in range(0, len(data)):
                item = data[i]
                if len(data[i]) < 1:
                    item = "0"
                if i == 0:
                    str1 = "'" + item + "'"
                else:
                    str1 += ",'" + item + "'"

            sql += "(" + str1 + ")"

        sql = sql.replace('nan', 'null').replace('None', 'null').replace('none', 'null')
        cursor.execute(sql)

        db.commit()
    except Exception as e:
        print(e)
        print(sql)
        return False

    return True


def queryKdata(code):
    global nowTime
    global lastTime, isbfgx
    global sql_dk

    try:
        sql0 = "SELECT date FROM `fa_stock_hsl` WHERE code = '" + code + "' ORDER BY id DESC LIMIT 1 "
        result = cursor.execute(sql0)
        db.commit()
        if result:
            date = cursor.fetchone()
            lastTime = (datetime.datetime(year=date[0].year, month=date[0].month, day=date[0].day)
                        + datetime.timedelta(days=1)).strftime('%Y-%m-%d')
            isbfgx = 1
    except Exception as e:
        print(e)

    rs = bs.query_history_k_data_plus(code, "date,code,turn,pctChg,close",
                                      start_date=lastTime, end_date=nowTime,
                                      frequency="d", adjustflag="2")  # frequency="d"取日k线，adjustflag="3"默认不复--前复权-2
    data_list = []
    while (rs.error_code == '0') & rs.next():
        data_list.append(rs.get_row_data())

    if len(data_list) < 1:
        return 0

    saveK2DB(data_list)
    return 1



#### 登陆系统 ####
lg = bs.login()
rs = bs.query_all_stock(day="2019-04-30")

#### 打印结果集 ####
# sh.600000～sz.300773
num = 0
isFirst = 0
data_list = []
while (rs.error_code == '0') & rs.next():
    one = rs.get_row_data()
    if isFirst == 0 and one[0] == "sh.600000":
        isFirst = 1

    if isFirst == 1 or num == 0:
        result = queryKdata(one[0])
        if result == 0:
            num += 1
            continue
        print('----Finish---' + str(num))
        num += 1
        if one[0] == "sz.300773":
            break

        # if num > 10:
        #     break

#### 登出系统 ####
bs.logout()

cursor.close()
db.close()

print('----OK---')
